/***********************************************************************************
 程序的大部分数据都是从数据库中获取
 除了下啦刷新的时候直接从网络获取数据
 
 表中的所有字段的类型都是 字符串 便于操作
 
 **********************************************************************************/

#import "QKSqlManger.h"
#import <CommonCrypto/CommonDigest.h>
#import "JSONKit.h"

@implementation QKSqlManger


#pragma mark - 创建数据库
//获取sqlite路径
+ (NSString *)sqlitepath
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
    NSString *documentsDirectory =[paths objectAtIndex:0];
    NSString *sqlname = [NSString stringWithFormat:@"skyTravel.sqlite"];
    NSString *sqlPath =[documentsDirectory stringByAppendingPathComponent:sqlname];
    
    return sqlPath;
}
//判断文件是否存在
+ (BOOL)is_file_exist
{
    NSFileManager *file_manager = [NSFileManager defaultManager];
    return [file_manager fileExistsAtPath:[QKSqlManger sqlitepath]];
}
//创建数据库
+ (void)createDB
{
    
    //先要判断数据库创建没有 创建过就不要在创建了
    NSFileManager * fileManager = [NSFileManager defaultManager];
    
    if ([fileManager fileExistsAtPath:[QKSqlManger sqlitepath]])
    {
        NSLog(@"数据库已经创建了，还要重新创建数据库吗？");
        
    }else
    {
        FMDatabase *db = [FMDatabase databaseWithPath:[QKSqlManger sqlitepath]];
        
        NSLog(@"%@",[QKSqlManger sqlitepath]);
        
        if ([db open])
        {
            NSLog(@"数据库创建成功！");
            
            
            [db close];
            
        }else
        {
            NSLog(@"数据库创建失败");
        }
        
    }
}
//复制本地数据库 到 docments
- (BOOL)copyLocalSQLliteToDoc
{
    NSFileManager*fileManager =[NSFileManager defaultManager];
    NSError*error;
    NSString *localpath = [[NSBundle mainBundle] pathForResource:@"bcj" ofType:@"sqlite"];
    NSString *sqlPath = [QKSqlManger sqlitepath];
    
    if([fileManager fileExistsAtPath:sqlPath] == NO){
        
        if ([fileManager copyItemAtPath:localpath toPath:sqlPath error:&error]) {
            NSLog(@"OK");
            return YES;
        }else{
            NSLog(@"no");
            return NO;
        }
    }
    return NO;
}


#pragma mark - 创建数据表
+ (void)creatTable:(NSString *)tableName
         tableKeys:(NSArray  *)keys

{
    
    //找到数据库所在的位置
    FMDatabase *db = [FMDatabase databaseWithPath:[QKSqlManger sqlitepath]];
    //先判断数据库是否存在，如果不存在，创建数据库
    
    [db open];
    //判断数据库中是否已经存在这个表，如果不存在则创建该表
    if([db tableExists:tableName] == NO)
    {
        //创建sql语句
        NSMutableString *keyString = [NSMutableString stringWithFormat:@"CREATE TABLE %@",tableName];
        
        
        if ([keys count] > 0)
        {
            //有字段需要创建
            
            
            //最前面的和最后面的药做特殊处理
            NSString *firstString = [NSString stringWithFormat:@"(%@ TEXT",[keys objectAtIndex:0]];
            [keyString appendString:firstString];
            
            for (int index = 1; index < ([keys count]); index ++)
            {
                //中间的每一项需要加逗号
                NSString *centerString = [NSString stringWithFormat:@",%@ TEXT",[keys objectAtIndex:index]];
                
                [keyString appendString:centerString];
            }
            
            
            //加到最后了要添加末尾结束符
            [keyString appendString:@")"];
            
            
            
            
        }else
        {
            NSLog(@"字段都没有，还要创建表吗？");
        }
        
        
        [db executeUpdate:keyString];
        
        NSLog(@"创建完成");
        
        
    }else
    {
        NSLog(@"%@已经存在,不需要重新创建了！！！！！",tableName);
    }
    
    [db close];
}
#pragma mark - 插入数据 先删除在插入
//插入一组数据
+ (void)insertTable:(NSString   *)tableName
         groupValue:(NSArray    *)values
         primaryKey:(NSString   *)key

{
    
    FMDatabase *db = [FMDatabase databaseWithPath:[QKSqlManger sqlitepath]];
    
    [db open];
    //判断表是否存在
    if ([db tableExists:tableName])
    {
        
        NSLog(@"向%@中插入新数据",tableName);
        for (int index = 0; index < [values count]; index ++)
        {
            //插入前需要做查找 没有在插入，有的话需要先删除在插入数据
            NSDictionary *dic = [values objectAtIndex:index];
            NSString *keyValue = [dic objectForKey:key];
            
            
            FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[QKSqlManger sqlitepath]];
            
            NSString *deleteSql = [NSString stringWithFormat:@"delete from %@ where %@ = %@",tableName,key,keyValue];
            NSString *insertSql = [QKSqlManger createSql:dic tableName:tableName];
            
            [queue inDatabase:^(FMDatabase *db) {
                
                [db beginTransaction];
                //先做删除操作
                [db executeUpdate:deleteSql];
                //删除数据后再做插入操作，这样可以保证不重复插入相同的数据
                [db executeUpdate:insertSql];
                
                [db commit];
                
            }];
            
        }
        
        
        
    }else
    {
        
        
        
        NSLog(@"表不存在,需要创建一张新表，还是表名出线了问题！");
        
    }
    
    [db close];
    
    
    
}

+ (void)updateTable:(NSString   *)tableName
         groupValue:(NSArray    *)values
            success:(void(^)())success
            failure:(void(^)())failure

{
    
    FMDatabase *db = [FMDatabase databaseWithPath:[QKSqlManger sqlitepath]];
    
    [db open];
    //判断表是否存在
    if ([db tableExists:tableName])
    {
        
        NSLog(@"向%@中插入新数据",tableName);
        for (int index = 0; index < [values count]; index ++)
        {
            
            
            FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[QKSqlManger sqlitepath]];
            
            NSString *deleteSql = [NSString stringWithFormat:@"delete from %@",tableName];
            
            
            
            [queue inDatabase:^(FMDatabase *db) {
                
                [db beginTransaction];
                
                //先删除表中的数据
                [db executeUpdate:deleteSql];
                
                
                for (int index = 0; index < [values count]; index ++)
                {
                    
                    //插入前需要做查找 没有在插入，有的话需要先删除在插入数据
                    NSDictionary *dic = [values objectAtIndex:index];
                    
                    NSString *insertSql = [QKSqlManger createSql:dic tableName:tableName];
                    
                    //删除数据后再做插入操作，这样可以保证不重复插入相同的数据
                    [db executeUpdate:insertSql];
                    
                }
                
                
                
                [db commit];
                
            }];
            
        }
        
        if (success)
        {
            success();
        }
        
    }else
    {
        if ([values count] > 0)
        {
            
            NSDictionary *dic = [values objectAtIndex:0];
            NSArray *keys = [dic allKeys];
            
            //创建sql语句
            NSMutableString *keyString = [NSMutableString stringWithFormat:@"CREATE TABLE %@",tableName];
            
            
            if ([keys count] > 0)
            {
                //有字段需要创建
                
                //最前面的和最后面的药做特殊处理
                NSString *firstString = [NSString stringWithFormat:@"(%@ TEXT",[keys objectAtIndex:0]];
                [keyString appendString:firstString];
                
                for (int index = 1; index < ([keys count]); index ++)
                {
                    //中间的每一项需要加逗号
                    NSString *centerString = [NSString stringWithFormat:@",%@ TEXT",[keys objectAtIndex:index]];
                    
                    [keyString appendString:centerString];
                }
                
                //加到最后了要添加末尾结束符
                [keyString appendString:@")"];
                
                
                FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:[QKSqlManger sqlitepath]];
                
                [queue inDatabase:^(FMDatabase *db) {
                    
                    [db beginTransaction];
                    
                    //先创建数据表
                    [db executeUpdate:keyString];
                    
                    for (int index = 0; index < [values count]; index ++)
                    {
                        
                        //插入前需要做查找 没有在插入，有的话需要先删除在插入数据
                        NSDictionary *dic = [values objectAtIndex:index];
                        
                        NSString *insertSql = [QKSqlManger createSql:dic tableName:tableName];
                        
                        //删除数据后再做插入操作，这样可以保证不重复插入相同的数据
                        [db executeUpdate:insertSql];
                        
                    }
                    
                    [db commit];
                    
                }];
                
                if (success)
                {
                    success();
                }
                
            }else
            {
                if (failure)
                {
                    failure(@"字段都没有，还要创建表吗？");
                }
                
            }
            
            
        }
        
        
        
    }
    
    [db close];
    
    
    
}


//构造插入一组数据sql的语句
+ (NSString *)createSql:(NSDictionary *)groupDic
              tableName:(NSString *)tableName
{
    //获取所有key 作为要插入的字段
    NSArray *allkey = [groupDic allKeys];
    
    //转成字符串
    NSString *contentkey = [allkey componentsJoinedByString:@","];
    
    //获取要出入的数据
    NSMutableArray *valueArray = [NSMutableArray array];
    for (int index = 0; index < [allkey count]; index ++)
    {
        
        NSString *value = [groupDic objectForKey:[allkey objectAtIndex:index]];
        NSString *svalue = [NSString stringWithFormat:@"'%@'",value];
        
        [valueArray addObject:svalue];
    }
    
    NSString *contentValue = [valueArray componentsJoinedByString:@","];
    //构造sql 插入语句
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@(%@) values(%@)",tableName,contentkey,contentValue];
    
    NSLog(@"sql : %@",sql);
    
    return sql;
}


#pragma mark - 查找数据
//查找一组数据
+ (NSMutableArray *)selectTable:(NSString *)tableName
                      tableKeys:(NSArray  *)keys
                          where:(NSString *)where
{
    FMDatabase *db = [FMDatabase databaseWithPath:[QKSqlManger sqlitepath]];
    
    [db open];
    if ([db tableExists:tableName])
    {
        //根据需要的条件查找所需表中 需要的字段
        NSMutableArray *resultArray = [[NSMutableArray alloc] initWithCapacity:0];
        
        
        //先要判断表存不存在，如果不存在就不查了啊
        if ([db open])
        {
            NSString *sql;
            if (where != nil)
            {
                sql = [NSString stringWithFormat:@"SELECT * FROM %@ %@",tableName,where];
                
            }else
            {
                sql = [NSString stringWithFormat:@"SELECT * FROM %@",tableName];
            }
            
            FMResultSet *rs = [db executeQuery:sql];
            
            while ([rs next]){
                
                NSMutableDictionary *resultDic = [NSMutableDictionary dictionaryWithCapacity:0];
                for (int index = 0; index < [keys count]; index ++)
                {
                    
                    //字典中需要包含的字段和他的值
                    NSString *key = [keys objectAtIndex:index];
                    
                    //把数据添加到字典中
                    [resultDic setObject:[rs stringForColumn:key] forKey:key];
                    
                    
                }
                
                [resultArray addObject:resultDic];
                
            }
            
        }
        
        [db close];
        
        
        
        return resultArray;
        
    }else
    {
        NSLog(@"数据表都不存在，还差个毛线啊");
        
        return nil;
    }
    
    
    
}

//查找一组数据
+ (void )selGroupData:(NSString *)tableName
            tableKeys:(NSArray  *)keys
                where:(NSString *)where
             susscess:(void (^)(NSArray *result))sussess
              failure:(void (^)(NSString *error))failure
{
    FMDatabase *db = [FMDatabase databaseWithPath:[QKSqlManger sqlitepath]];
    
    [db open];
    if ([db tableExists:tableName])
    {
        //根据需要的条件查找所需表中 需要的字段
        NSMutableArray *resultArray = [[NSMutableArray alloc] initWithCapacity:0];
        
        
        //先要判断表存不存在，如果不存在就不查了啊
        if ([db open])
        {
            NSString *sql;
            if (where != nil)
            {
                sql = [NSString stringWithFormat:@"SELECT * FROM %@ %@",tableName,where];
                
            }else
            {
                sql = [NSString stringWithFormat:@"SELECT * FROM %@",tableName];
            }
            
            FMResultSet *rs = [db executeQuery:sql];
            
            while ([rs next]){
                
                NSMutableDictionary *resultDic = [NSMutableDictionary dictionaryWithCapacity:0];
                for (int index = 0; index < [keys count]; index ++)
                {
                    
                    //字典中需要包含的字段和他的值
                    NSString *key = [keys objectAtIndex:index];
                    
                    //把数据添加到字典中
                    [resultDic setObject:[rs stringForColumn:key] forKey:key];
                    
                    
                }
                
                [resultArray addObject:resultDic];
                
            }
            
        }
        
        [db close];
        
        if (sussess) {
            
            sussess(resultArray);
        }
        
        
    }else
    {
        NSLog(@"数据表都不存在，还差个毛线啊");
        
        if (failure) {
            
            failure(@"数据表都不存在，还差个毛线啊");
        }
        
    }
    
    
    
}


//一条一条的从表中查找数据
+ (void)selectTable:(NSString *)tableName
          tableKeys:(NSArray  *)keys
              where:(NSString *)where
            success:(void (^)(NSDictionary *dic))sucess
            failure:(void (^)(NSString   *error))failure

{
    FMDatabase *db = [FMDatabase databaseWithPath:[QKSqlManger sqlitepath]];
    
    [db open];
    if ([db tableExists:tableName])
    {
        
        
        //先要判断表存不存在，如果不存在就不查了啊
        if ([db open])
        {
            NSString *sql;
            if (where != nil)
            {
                sql = [NSString stringWithFormat:@"SELECT * FROM %@ %@",tableName,where];
                
            }else
            {
                sql = [NSString stringWithFormat:@"SELECT * FROM %@",tableName];
            }
            
            FMResultSet *rs = [db executeQuery:sql];
            
            while ([rs next]){
                
                NSMutableDictionary *resultDic = [NSMutableDictionary dictionaryWithCapacity:0];
                for (int index = 0; index < [keys count]; index ++)
                {
                    
                    //字典中需要包含的字段和他的值
                    NSString *key = [keys objectAtIndex:index];
                    
                    //把数据添加到字典中
                    [resultDic setObject:[rs stringForColumn:key] forKey:key];
                    
                    
                }
                
                if (sucess)
                {
                    sucess(resultDic);
                }
                
                
                
            }
            
        }
        
        [db close];
        
        
    }else
    {
        if (failure)
        {
            failure(@"数据表都不存在，还查个毛线啊");
        }
        
    }
    
}








//获取数据



+ (BOOL) deleteTable:(NSString *)tableName
{
    FMDatabase *db = [FMDatabase databaseWithPath:[QKSqlManger sqlitepath]];
    
    NSString *sqlstr = [NSString stringWithFormat:@"DROP TABLE %@", tableName];
    if (![db executeUpdate:sqlstr])
    {
        return NO;
    }
    
    return YES;
}
@end
